use sqlx::{PgExecutor, Postgres, QueryBuilder, Result};

use crate::{filter, model, utils};

pub async fn insert<'a>(e: impl PgExecutor<'a>, m: &model::url::UrlLogs) -> Result<String> {
    let id = utils::id::new();

    let mut q =
        QueryBuilder::new(r#"INSERT INTO "url_logs" ("id","url","ip","user_agent","dateline") "#);

    q.push_values(&[m], |mut b, g| {
        b.push_bind(&id)
            .push_bind(&g.url)
            .push_bind(&g.ip)
            .push_bind(&g.user_agent)
            .push_bind(&g.dateline);
    });

    q.build().execute(e).await?;
    Ok(id)
}

pub async fn find<'a>(e: impl PgExecutor<'a>, id: &str) -> Result<Option<model::url::UrlWithLogs>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "url_id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired", "url_dateline", "id", "ip", "user_agent", "dateline" FROM "v_url_logs" WHERE id="#,
    );
    q.push_bind(id);

    q.build_query_as().fetch_optional(e).await
}

pub async fn list_data<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::url::ListLogs<'a>,
) -> Result<Vec<model::url::UrlWithLogs>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "url_id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired", "url_dateline", "id", "ip", "user_agent", "dateline" FROM "v_url_logs" WHERE 1=1"#,
    );

    build_list_query(&mut q, f);

    let order = match f.order {
        Some(v) => v,
        None => "id DESC",
    };
    q.push(" ORDER BY ").push_bind(order);

    q.push(" LIMIT ")
        .push_bind(f.pagination.page_size())
        .push(" OFFSET ")
        .push_bind(f.pagination.offset());

    q.build_query_as().fetch_all(e).await
}

pub async fn list_count<'a>(e: impl PgExecutor<'a>, f: &filter::url::ListLogs<'a>) -> Result<u32> {
    let mut q = QueryBuilder::new(r#"SELECT COUNT(*) FROM "v_url_logs" WHERE 1=1"#);

    build_list_query(&mut q, f);

    let c: (i64,) = q.build_query_as().fetch_one(e).await?;

    Ok(c.0 as u32)
}

fn build_list_query<'a>(q: &mut QueryBuilder<Postgres>, f: &filter::url::ListLogs<'a>) {
    if let Some(v) = f.url {
        let param = format!("%{v}%");
        q.push(" AND url ILIKE ").push_bind(param);
    }

    if let Some(v) = f.url_id {
        q.push(" AND url_id=").push_bind(v.to_string());
    }
    if let Some(v) = f.user_id {
        q.push(" AND user_id=").push_bind(v.to_string());
    }

    if let Some(v) = f.email {
        let param = format!("%{v}%");
        q.push(" AND email ILIKE ").push_bind(param);
    }

    if let Some(v) = &f.dateline_range {
        q.push(" AND (dateline BETTEN ")
            .push_bind(v.start)
            .push(" AND ")
            .push_bind(v.end)
            .push(")");
    }
}
